Skip Headers

Oracle® Database 2 Day DBA 10g Release 2 (10.2) Part Number B14196-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# Administering Roles

System privileges, object privileges, and roles provide a basic level of database security. They are designed to control user access to data and to limit the kinds of SQL statements that users can execute.

## About Privileges and Roles

Roles are groupings of privileges that you can use to create different levels of database access. For example, you can create a role for application developers that enable users to create tables and programs.

You can grant privileges and roles to other users only when you possess the necessary privilege. The granting of roles and privileges starts at the administrator level. At database creation, the administrative user `SYS` is created and granted all system privileges and predefined Oracle roles. User `SYS` can then grant privileges and roles to other users and also grant those users the right to grant specific privileges to others.

Table 7-2 provides descriptions and examples of privileges and roles.

Table 7-2 Privileges and Roles

Privilege or Role | Description | Examples
--- | --- | ---
System privilege | An Oracle-defined privilege usually granted only to and by administrators. System privileges enable users to perform specific database operations. | The following are examples of system privileges that can be granted to users: `CREATE` `TABLE` allows grantee to create tables in the grantee's schema. `CREATE` `USER` allows grantee to create users in the database. `CREATE` `SESSION` allows grantee to connect to an Oracle database to create a user session.
Object privilege | A privilege that controls access to a specific object. | The following examples are object privileges that can be granted to users: `SELECT ON hr.employees TO myuser` `INSERT ON hr.employees TO myuser`
Role | A group of privileges or other roles | The following examples are Oracle-defined roles: `CONNECT` is a role that Enterprise Manager automatically grants to a user when you create a user as shown in "Creating Users". This role has the `CREATE` `SESSION` privilege. `RESOURCE` extends the privileges of a user beyond those granted by the `CONNECT` role. It includes `CREATE` `PROCEDURE`, `CREATE` `TRIGGER`, and other system privileges. `DBA` is the standard role that can be granted by an administrator to another administrator. It includes all system privileges and should only be granted to the most trusted and qualified of users. Assigning this role to a user enables the user to administer the database. You can create your own roles if you have been granted this privilege.

See Also: Oracle Database SQL Reference for a list of Oracle-defined privileges and roles

## About Administrative Accounts

The following administrative accounts are automatically created when Oracle Database is installed:

- SYS

- SYSTEM

### SYS

When you create an Oracle database, the user `SYS` is automatically created and granted the `DBA` role.

All base tables and views for the database data dictionary are stored in the schema `SYS`. These base tables and views are critical for the operation of Oracle Database. To maintain the integrity of the data dictionary, tables in the `SYS` schema are manipulated only by the database. They should never be modified by any user or database administrator. Also, you should not create any tables in the schema of user `SYS`, although you can change the storage parameters of the data dictionary settings if necessary.

Ensure that most database users are never able to connect to Oracle Database with the `SYS` account.

### SYSTEM

When you create an Oracle Database, the user `SYSTEM` is also automatically created and granted the `DBA` role.

The `SYSTEM` user can create additional tables and views that display administrative information as well as internal tables and views used by various Oracle Database options and tools. Never use the `SYSTEM` schema to store tables of interest to nonadministrative users.

A predefined `DBA` role is automatically created with every Oracle Database installation. This role contains most database system privileges. Therefore, you should grant the `DBA` role only to actual database administrators. The `DBA` role does not include the `SYSDBA` or `SYSOPER` system privileges.

### Administrative Privileges

`SYSDBA` and `SYSOPER` are administrative privileges required to perform basic database operations such as creating the database and instance startup and shutdown. Depending upon the level of authorization you require, you must have one of these privileges granted to you.

Note: The `SYSDBA` and `SYSOPER` system privileges allow access to a database instance even when the database is not open. Control of these privileges is totally outside of the database itself.

You can also think of the `SYSDBA` and `SYSOPER` privileges as types of connections that enable you to perform certain database operations for which privileges cannot be granted in any other way. For example, if you have the `SYSDBA` privilege, then you can connect to the database by specifying `CONNECT` `AS` `SYSDBA`.

See Also: Oracle Database Administrator's Guide for more the operations authorized with each privilege and an example

## Viewing Roles

You can use Enterprise Manager to view existing roles as follows:

1. In the Users & Privileges section of the Administration home page, click Roles.

   Figure 7-1 Users & Privileges

   Description of "Figure 7-1 Users & Privileges"

   The Roles page appears. From this page you can create, edit, view, or delete roles. The structure and functionality of the Roles page is similar to that of the Users page shown in Figure 7-2.

2. Select the `CONNECT` role.

3. Click View.

   The View page appears. In this page you can see all of the privileges and roles associated with the `CONNECT` role.

## Creating Roles

You can create a secure role with the privileges necessary for application development. You can then grant the role to other roles or users depending on the level of data access required by the user.

See Also: Oracle Database Security Guide for more information on administering user security, roles, and privileges

In this exercise, you create an application developer role called `APPDEV`.

To create the `APPDEV` role:

1. In the Users & Privileges section of the Administration home page, click Roles.

   The Roles page appears.

2. Click Create.

   The Create Role General page appears.

3. In the Name field, enter the name of the new role. For instance, enter `APPDEV` to create a new role for application developers.

4. Click OK.

   A page appears with a list of all roles, including the `APPDEV` role that you just created. You can now modify this new role by adding the required privileges.

## Modifying Roles

You can add roles, privileges, and consumer groups to roles. In this exercise, you add the basic system privileges shown in Table 7-3, which allow the creation of various objects, to the `APPDEV` role that you created previously. These objects are described in Chapter 8, "Managing Schema Objects".

Table 7-3 APPDEV Privileges

Privilege | Description
--- | ---
CREATE TABLE | Enables user to create tables in his schema.
CREATE VIEW | Enables user to create views in his schema.
CREATE PROCEDURE | Enables user to create procedures in his schema.
CREATE TRIGGER | Enables user to create triggers in his schema.
CREATE SEQUENCE | Enables user to create sequences in his schema.
CREATE SYNONYM | Enables user to create synonyms in his schema.

To modify the `APPDEV` role:

1. In the Users & Privileges section of the Administration home page, click Roles.

   The Roles page appears.

2. From the list of roles, select APPDEV and click Edit.

3. Click System Privileges to navigate to the System Privileges property page.

   The System Privilege column should display no items.

4. Click Edit List.

   The Modify System Privileges page appears.

5. In the Available System Privileges list, double-click the privileges listed in Table 7-3 to add them to the Selected System Privileges list.

6. Click OK.

   You are returned to the Edit Role: APPDEV page.

7. Click Apply.

   A confirmation message should appear saying that the role has been modified successfully.

## Dropping Roles

In this exercise, you drop to the `APPDEV` role that you created in "Dropping Roles".

To drop the `APPDEV` role:

1. In the Users & Privileges section of the Administration home page, click Roles.

   The Roles page appears.

2. Select the `APPDEV` role and click Delete.

   A confirmation page appears.

3. Click Yes.

   A confirmation message indicates that the role has been deleted successfully.